﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsCustomersInfoDAL.cs
/// 作  者：Kidy
/// 时  间：2006.11.21
/// 说  明：ClsCustomersInfoDAL类用于客户信息相关处理
/// </summary>
public class ClsCustomersInfoDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取确认过的银行信息
    /// </summary>
    /// <returns>返回客户信息数据集</returns>
    public DataSet GetAuditBankInfo()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetAuditBankInfo", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取客户信息
    /// </summary>
    /// <param name="Type">0全部，1确认过的，2未确认的</param>
    /// <returns>返回客户信息数据集</returns>
    public DataSet GetCustomersInfo(int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersInfo", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 弹出查询获取客户信息
    /// </summary>
    /// <param name="Param">查询参数</param>
    /// <param name="Type">查询方式(0为精确,1为模糊)</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <returns>返回具有部门和客户类别权限的客户信息</returns>
    public DataSet GetCustomersInfo(string Param, int Type, string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersInfoToQuery", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 搜索客户信息
    /// </summary>
    /// <param name="Key">搜索关键字</param>
    /// <returns>返回搜索信息数据集</returns>
    public DataSet SearchCustomersInfo(string Key)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_SearchCustomers", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@key", Key);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户编号是否存在
    /// </summary>
    /// <param name="Cus_No">客户编号</param>
    /// <returns>返回－1检验失败，0编号不存在，大于0存在</returns>
    public int CheckUpCustomersID(string Cus_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomersID", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = Cus_No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 添加或修改客户基本信息
    /// </summary>
    /// <param name="ID">ID号(添加时为-1)</param>
    /// <param name="No">客户编号</param>
    /// <param name="Name">客户简称</param>
    /// <param name="Desc">客户全称</param>
    /// <param name="Tel">联系电话</param>
    /// <param name="Fax">传真号码</param>
    /// <param name="Addr">地址</param>
    /// <param name="Postno">邮编</param>
    /// <param name="Corporation">公司法人</param>
    /// <param name="Linkman">联系人</param>
    /// <param name="Email">EMAIL</param>
    /// <param name="Tel1">联系电话</param>
    /// <param name="Fax1">传真</param>
    /// <param name="DeptNo">所属部门</param>
    /// <param name="Principal">负责人</param>
    /// <param name="Custype">客户类型</param>
    /// <param name="Cussort">客户类别</param>
    /// <param name="Bank">开户行</param>
    /// <param name="Accounts">帐号</param>
    /// <param name="Revenue">税号</param>
    /// <param name="Remark">备注</param>
    /// <param name="Oper">操作员</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomersInfo(int ID, string No, string Name, string Desc, string Tel, string Fax, string Addr, string Postno, string Corporation, string Linkman, string Email, string Tel1, string Fax1, string DeptNo, string Principal, int Custype, string Cussort, string Bank, string Accounts, string Revenue, string Remark, string Oper)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddCustomersInfo", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@name", SqlDbType.NVarChar, 50);
        com.Parameters["@name"].Value = Name;
        com.Parameters.Add("@desc", SqlDbType.NVarChar, 255);
        com.Parameters["@desc"].Value = Desc;
        com.Parameters.Add("@tel", SqlDbType.NVarChar, 50);
        com.Parameters["@tel"].Value = Tel;
        com.Parameters.Add("@fax", SqlDbType.NVarChar, 50);
        com.Parameters["@fax"].Value = Fax;
        com.Parameters.Add("@addr", SqlDbType.NVarChar, 255);
        com.Parameters["@addr"].Value = Addr;
        com.Parameters.Add("@postno", SqlDbType.NVarChar, 50);
        com.Parameters["@postno"].Value = Postno;
        com.Parameters.Add("@corporation", SqlDbType.NVarChar, 50);
        com.Parameters["@corporation"].Value = Corporation;
        com.Parameters.Add("@linkman", SqlDbType.NVarChar, 50);
        com.Parameters["@linkman"].Value = Linkman;
        com.Parameters.Add("@email", SqlDbType.NVarChar, 255);
        com.Parameters["@email"].Value = Email;
        com.Parameters.Add("@tel1", SqlDbType.NVarChar, 50);
        com.Parameters["@tel1"].Value = Tel1;
        com.Parameters.Add("@fax1", SqlDbType.NVarChar, 50);
        com.Parameters["@fax1"].Value = Fax1;
        com.Parameters.Add("@dept", SqlDbType.NVarChar, 50);
        com.Parameters["@dept"].Value = DeptNo;
        com.Parameters.Add("@principal", SqlDbType.NVarChar, 50);
        com.Parameters["@principal"].Value = Principal;
        com.Parameters.Add("@custype", SqlDbType.Int, 4);
        com.Parameters["@custype"].Value = Custype;
        com.Parameters.Add("@cussort", SqlDbType.NVarChar, 50);
        com.Parameters["@cussort"].Value = Cussort;
        com.Parameters.Add("@bank", SqlDbType.NVarChar, 50);
        com.Parameters["@bank"].Value = Bank;
        com.Parameters.Add("@accounts", SqlDbType.NVarChar, 50);
        com.Parameters["@accounts"].Value = Accounts;
        com.Parameters.Add("@revenue", SqlDbType.NVarChar, 50);
        com.Parameters["@revenue"].Value = Revenue;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 50);
        com.Parameters["@oper"].Value = Oper;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变客户信息状态
    /// </summary>
    /// <param name="ID">ID</param>
    /// <param name="State">状态(0确认，1取消确认，2作废，3彻底作废)</param>
    /// <param name="Auditor">操作员</param>
    /// <returns>返回确认是否成功</returns>
    public Boolean UpdateCustomersState(int ID, int State, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateCustomersState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@state", SqlDbType.Int);
        com.Parameters["@state"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 50);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据客户ID查询客户价格
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <returns></returns>
    public DataSet GetCustomersPrice(int Cus_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersPrice", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@id"].Value = Cus_ID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户对应商品的价格是否存在
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="M_No">商品编号</param>
    /// <returns></returns>
    public int CheckUpCustomerPrice(int Cus_ID, string M_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomerPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改客户价格
    /// </summary>
    /// <param name="ID">客户价格ID(新增时为-1)</param>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="M_No">商品编号</param>
    /// <param name="M_Price">客户价格</param>
    /// <param name="Remark">备注</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomerPrice(int ID, int Cus_ID, string M_No, int M_Price, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateCustomerPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@cus_price", SqlDbType.Int, 4);
        com.Parameters["@cus_price"].Value = M_Price;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户价格
    /// </summary>
    /// <param name="ID">客户价格ID</param>
    /// <param name="Type">0为作废，1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCustomerPrice(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustomerPrice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据客户ID查询客户收货地址
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <returns></returns>
    public DataSet GetCustomersAddress(int Cus_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersAddress", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@id"].Value = Cus_ID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户对应的收货地址是否存在
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">地址编号</param>
    /// <returns></returns>
    public int CheckUpCustomerAddress(int Cus_ID, string No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomerAddress", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改客户地址
    /// </summary>
    /// <param name="ID">客户地址ID(新增时为-1)</param>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">地址编号</param>
    /// <param name="Name">地址简称</param>
    /// <param name="Desc">详细地址</param>
    /// <param name="Zip">邮编</param>
    /// <param name="Default">是否默认</param>
    /// <param name="Stop">是否停用</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomerAddress(int ID, int Cus_ID, string No, string Name, string Desc, string Zip, bool Default, int Stop)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateCustomerAddress", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@name", SqlDbType.NVarChar, 255);
        com.Parameters["@name"].Value = Name;
        com.Parameters.Add("@desc", SqlDbType.NVarChar, 255);
        com.Parameters["@desc"].Value = Desc;
        com.Parameters.Add("@zip", SqlDbType.NVarChar, 255);
        com.Parameters["@zip"].Value = Zip;
        com.Parameters.Add("@default", SqlDbType.Bit, 1);
        com.Parameters["@default"].Value = Default;
        com.Parameters.Add("@stop", SqlDbType.Bit, 1);
        com.Parameters["@stop"].Value = Stop;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户地址
    /// </summary>
    /// <param name="ID">客户地址ID</param>
    /// <param name="Type">0为作废，1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCustomerAddress(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustomerAddress", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据客户ID查询客户科室信息
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <returns></returns>
    public DataSet GetCustomersOffice(int Cus_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersOffice", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@id"].Value = Cus_ID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户对应的科室信息是否存在
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">科室编号</param>
    /// <returns></returns>
    public int CheckUpCustomerOffice(int Cus_ID, string No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomerOffice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改客户科室信息
    /// </summary>
    /// <param name="ID">客户科室信息ID(新增时为-1)</param>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">科室编号</param>
    /// <param name="Stop">是否停用</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomerOffice(int ID, int Cus_ID, string No, int Stop)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateCustomerOffice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@stop", SqlDbType.Bit, 1);
        com.Parameters["@stop"].Value = Stop;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户科室信息
    /// </summary>
    /// <param name="ID">科室信息ID</param>
    /// <param name="Type">0为作废，1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCustomerOffice(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustomerOffice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据客户ID查询客户许可证信息
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <returns></returns>
    public DataSet GetCustomersLicence(int Cus_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersLicence", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@id"].Value = Cus_ID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户对应的许可证信息是否存在
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">许可证编号</param>
    /// <returns></returns>
    public int CheckUpCustomerLicence(int Cus_ID, string No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomerLicence", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改客户许可证信息
    /// </summary>
    /// <param name="ID">许可证信息ID(新增时为-1)</param>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">科许可证号</param>
    /// <param name="Name">许可证名称</param>
    /// <param name="Corpname">单位名称</param>
    /// <param name="Principal">负责人</param>
    /// <param name="Fiatbound">许可范围</param>
    /// <param name="Virtualdate">有效日期</param>
    /// <param name="Corpaddr">单位地址</param>
    /// <param name="Organ">发证机关</param>
    /// <param name="Date">发证日期</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomerLicence(int ID, int Cus_ID, string No, string Name, string Corpname, string Principal, string Fiatbound, DateTime Virtualdate, string Corpaddr, string Organ, DateTime Date)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateCustomerLicence", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@name", SqlDbType.NVarChar, 50);
        com.Parameters["@name"].Value = Name;
        com.Parameters.Add("@corpname", SqlDbType.NVarChar, 50);
        com.Parameters["@corpname"].Value = Corpname;
        com.Parameters.Add("@principal", SqlDbType.NVarChar, 50);
        com.Parameters["@principal"].Value = Principal;
        com.Parameters.Add("@fiatbound", SqlDbType.NVarChar, 255);
        com.Parameters["@fiatbound"].Value = Fiatbound;
        com.Parameters.Add("@virtualdate", SqlDbType.DateTime, 8);
        com.Parameters["@virtualdate"].Value = Virtualdate;
        com.Parameters.Add("@corpaddr", SqlDbType.NVarChar, 255);
        com.Parameters["@corpaddr"].Value = Corpaddr;
        com.Parameters.Add("@organ", SqlDbType.NVarChar, 50);
        com.Parameters["@organ"].Value = Organ;
        com.Parameters.Add("@date", SqlDbType.DateTime, 8);
        com.Parameters["@date"].Value = Date;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户许可证信息
    /// </summary>
    /// <param name="ID">许可证信息ID</param>
    /// <param name="Type">0为作废，1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCustomerLicence(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustomerLicence", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据客户ID查询客户营业执照信息
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <returns></returns>
    public DataSet GetCustomersCharter(int Cus_ID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCustomersCharter", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@id"].Value = Cus_ID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验客户对应营业执照信息是否存在
    /// </summary>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">注册号</param>
    /// <returns></returns>
    public int CheckUpCustomerCharter(int Cus_ID, string No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpCustomerCharter", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 新增或修改客户营业执照信息
    /// </summary>
    /// <param name="ID">营业执照信息ID(新增时为-1)</param>
    /// <param name="Cus_ID">客户ID</param>
    /// <param name="No">注册号</param>
    /// <param name="Name">单位名称</param>
    /// <param name="Legal">法定代表</param>
    /// <param name="Economykind">经济性质</param>
    /// <param name="Regbankroll">注册资金</param>
    /// <param name="Dealinbound">经营范围</param>
    /// <param name="Dealindome">经营方式</param>
    /// <param name="Regaddr">注册地址</param>
    /// <param name="Organ">发照机关</param>
    /// <param name="Date">发照日期</param>
    /// <returns></returns>
    public Boolean AddOrUpdateCustomerCharter(int ID, int Cus_ID, string No, string Name, string Legal, string Economykind, string Regbankroll, string Dealinbound, string Dealindome, string Regaddr, string Organ, DateTime Date)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateCustomerCharter", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@cus_id", SqlDbType.Int, 4);
        com.Parameters["@cus_id"].Value = Cus_ID;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 100);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@name", SqlDbType.NVarChar, 50);
        com.Parameters["@name"].Value = Name;
        com.Parameters.Add("@legal", SqlDbType.NVarChar, 50);
        com.Parameters["@legal"].Value = Legal;
        com.Parameters.Add("@economykind", SqlDbType.NVarChar, 50);
        com.Parameters["@economykind"].Value = Economykind;
        com.Parameters.Add("@regbankroll", SqlDbType.NVarChar, 50);
        com.Parameters["@regbankroll"].Value = Regbankroll;
        com.Parameters.Add("@dealinbound", SqlDbType.NVarChar, 255);
        com.Parameters["@dealinbound"].Value = Dealinbound;
        com.Parameters.Add("@dealindome", SqlDbType.NVarChar, 100);
        com.Parameters["@dealindome"].Value = Dealindome;
        com.Parameters.Add("@regaddr", SqlDbType.NVarChar, 255);
        com.Parameters["@regaddr"].Value = Regaddr;
        com.Parameters.Add("@organ", SqlDbType.NVarChar, 50);
        com.Parameters["@organ"].Value = Organ;
        com.Parameters.Add("@date", SqlDbType.DateTime, 8);
        com.Parameters["@date"].Value = Date;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废客户营业执照信息
    /// </summary>
    /// <param name="ID">营业执照信息ID</param>
    /// <param name="Type">0为作废，1为彻底作废</param>
    /// <returns></returns>
    public Boolean DeleteCustomerCharter(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteCustomerCharter", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
